<?php

class Model_ZendDb_FeedService implements Model_FeedService 
{
    /**
     * @var Zend_Db_Adapter_Abstract
     */
    private $_db;
    
    /**
     * @var Model_ZendDb_DbTable_Feed
     */
    private $_feed;
    
    /**
     * @var Model_ZendDb_DbTable_UserFeeds
     */
    private $_feedUser;
    
    /**
     * @var Model_ZendDb_DbTable_CategoryFeeds
     */
    private $_feedCategory;
    
    /**
     * @var Model_ZendDb_DbTable_News
     */
    private $_news;
    
    /**
     * @var Model_ZendDb_DbTable_UserNews
     */
    private $_userNews;
    
    /**
     * @var Model_ZendDb_DbTable_Homepagebox
     */
    private $_homePageBox;
    
    public function __construct()
    {
        $this->_feed = new Model_ZendDb_DbTable_Feed();
        $this->_db = $this->_feed->getAdapter();
        $this->_feedUser = new Model_ZendDb_DbTable_UserFeeds();
        $this->_feedCategory = new Model_ZendDb_DbTable_CategoryFeeds();
        $this->_news = new Model_ZendDb_DbTable_News();
        $this->_userNews = new Model_ZendDb_DbTable_UserNews();
        $this->_homePageBox = new Model_ZendDb_DbTable_Homepagebox();
    }
    
    /**
     * Check if a feed already exists
     * 
     * @param String $url
     * @return Model_Entity_Feed if the feed $url is present  
     */
    private function isFeedPresent($url) {
        $select = $this->_db->quoteInto('url = ?', $url);
        $result = $this->_feed->fetchAll($select);
        $feed = null;
        
        if (! 0 == count($result)) {
            $feed = new Model_Entity_Feed($result->current()->toArray());
        }
        
        return $feed;
    }
    
    /**
     * Check if $feed is already owned by $feed->user
     * 
     * @param Model_Entity_Feed $feed 
     * @return boolean
     */
    private function isFeedAlreadyOwned($feed) {
        $select = $this->_feedUser->select();
        $select->where('user_oid = ?', $feed->getCreatedBy()->getOid())  
               ->where('feed_oid = ?', $feed->getOid());
        
        $result = $this->_feedUser->fetchAll($select);
        
        return (0 == count($result)) ? false : true;      
    }
    
    /**
     * Check if a feed has no owners
     * 
     * @param int $feedID 
     * @return boolean     
     */
    private function hasFeedOwners($feedID) {
    	$select = $this->_db->select();
        
        $sql = "SELECT COUNT(*) AS elements FROM user_feed ".
               "WHERE feed_oid = $feedID";
        
        $result =  $this->_db->fetchAll($sql);

        return $result[0]['elements'] == 0 ? false : true;
    }
    
    /**
     * Chech if a feed is assigned to an homepagebox
     * 
     * @param int $feedID
     * @return boolean
     */
    private function isFeedInHomepageBox($feedID) {
    	$select = $this->_db->select();
        
        $sql = "SELECT COUNT(*) AS elements FROM homepagebox ".
               "WHERE feed_oid = $feedID";
        
        $result =  $this->_db->fetchAll($sql);

        return $result[0]['elements'] == 0 ? false : true;
    }
    
    /**
     * Check if $feed is already present in the category
     * 
     * @param Model_Entity_Feed $feed 
     * @return boolean
     */
    private function isFeedInCategory($feed) {
    	$select = $this->_feedCategory->select();
    	$select->where('category_oid = ?', $feed->getCategory()->getOid())  
               ->where('feed_oid = ?', $feed->getOid());
        
        $result = $this->_feedUser->fetchAll($select);
        
        return (0 == count($result)) ? false : true;
    }
    
    /**
     * Retrieve e list of top ten feed in the given category
     * 
     * @param int $catID
     * @return array of Model_Entity_Feed
     * @see application/models/Model_FeedService#getTopTenFeeds($catID)
     */
    public function getTopTenFeeds($catID) {
    	$sql = "SELECT feed.*, count(*) AS quantity FROM feed ".
               "JOIN user_feed AS uf ON feed.oid = uf.feed_oid ".
    	       //"JOIN category_feed AS cf ON feed.oid = cf.feed_oid ".
    	       //"WHERE cf.category_oid = ? ".
    	       "WHERE uf.category_oid = ? ".
               "GROUP BY feed.oid ".
               "ORDER BY quantity DESC ".
               "LIMIT 10";
        
        $resultSet = $this->_db->fetchAll($sql, array($catID));
        
        $feeds = array();
        foreach($resultSet as $row) {
            $feeds[] = new Model_Entity_Feed($row);
        }
        
        return $feeds;
    }
    
    /**
     * Get all feed from user's preferred category
     * 
     * @param int $catID
     * @param int $userID
     * @return array of Model_Entity_Feed
     * @see application/models/Model_FeedService#getSuggestedFeed($catID, $userID)
     */
    public function getSuggestedFeed($catID, $userID) {
        $select = $this->_db->select();
        $sql = "SELECT feed.* FROM feed ".
               "JOIN category_feed AS cf ON cf.feed_oid = feed.oid ".
               "WHERE cf.category_oid = $catID AND feed.oid <> ALL ( ".
                    "SELECT feed.oid FROM feed ".
                    "JOIN category_feed AS cf ON cf.feed_oid = feed.oid ".
                    "JOIN user_feed AS uf ON uf.feed_oid = feed.oid ".
                    "WHERE cf.category_oid = $catID AND uf.user_oid = $userID )";
               
        $resultSet = $this->_db->fetchAll($sql);
        
        $feed = array();
        foreach ($resultSet as $row) {
            $feed[] = new Model_Entity_Feed($row);
        }

        return $feed;
    }
    
    /**
     * Get all user feed that needs to be upgraded
     * 
     * @param int $userID
     * @param int $interval update interval in seconds
     * @return array of Model_Entity_Feed
     * @see application/models/Model_FeedService#getFeedToUpgrade($userID, $interval)
     */
    public function getFeedToUpgrade($userID, $interval) {
    	$datetimeinterval = date('Y-m-d H:i:s', time() - $interval);
    	
        $select = $this->_db->select();
        $select->from('feed')
               ->join(array('uf' => 'user_feed'), 
                            'uf.feed_oid = feed.oid', null)
               ->where('uf.user_oid = ?', $userID)
               ->where('feed.lastSync <= ?', $datetimeinterval);
               
        //die($select->__toString());
        
        $resultSet = $this->_db->fetchAll($select);
        
        $feed = array();
        foreach ($resultSet as $row) {
            $feed[] = new Model_Entity_Feed($row);
        }

        return $feed;
    }
    
    /**
     * Get all boxes' feeds that needs to be upgraded
     * 
     * @param int $interval update interval in seconds
     * @return array of Model_Entity_Feed
     * @see application/models/Model_FeedService#getFeedBoxToUpgrade($interval)
     */
    public function getFeedBoxToUpgrade($interval) {
    	$datetimeinterval = date('Y-m-d H:i:s', time() - $interval);
    	
    	$select = $this->_db->select();
    	$select->from('feed')
    	       ->join(array('hb' => 'homepagebox'),
    	              'hb.feed_oid = feed.oid',null)
    	       ->where('feed.lastSync <= ?', $datetimeinterval);
    	       
    	$resultSet = $this->_db->fetchAll($select);
        
        $feed = array();
        foreach ($resultSet as $row) {
            $feed[] = new Model_Entity_Feed($row);
        }

        return $feed;
    }
    
    /**
     * Get all user feed beginning to a given category
     * 
     * @param int $userID
     * @param int $catID optional
     * @return array of Model_Entity_Feed
     * @see application/models/Model_FeedService#getUserFeed($catID, $userID)
     */
    public function getUserFeed($userID, $catID = null) {
    	$select = $this->_db->select();
        $select->from('feed')
               ->join(array('uf' => 'user_feed'), 
                            'uf.feed_oid = feed.oid', null)
               ->where('uf.user_oid = ?', $userID);
        
        if ($catID != null) {
        	/*$select->join(array('cf' => 'category_feed'), 
                            'cf.feed_oid = feed.oid', null)
        	       ->join(array('uc' => 'categories_users'),
        	                'cf.category_oid = uc.category_oid', null)
        	       ->where('uc.category_oid = ?', $catID)
        	       ->where('uc.user_oid = ?', $userID);*/
        	$select->where('uf.category_oid = ?', $catID);
        }
        //die($select->__toString());
        $resultSet = $this->_db->fetchAll($select);
        
        $feed = array();
        foreach ($resultSet as $row) {
        	$feed[] = new Model_Entity_Feed($row);
        }

        return $feed;
    }
    
    /**
     * Search for feed matching search criteria based on category and
     * contents of name,url and description fields
     * If no criteria are specified it will return the entire list of feeds
     * in the system
     * 
     * @param array $criteria an optional associative array  
     * @return Zend_Paginator_Adapter_Interface
	 * @see application/models/Model_FeedService#searchFeeds($criteria)
	 */
	public function searchFeeds($criteria = null) {
		$select = $this->_db->select();
        $select->from('feed');
        
		if ($criteria !== null) {
			if (isset($criteria['catID']) && !empty($criteria['catID'])) {
				$select->join(array('cf' => 'category_feed'), 
				              'cf.feed_oid = feed.oid', null)
		               ->where('cf.category_oid = ?', $criteria['catID']);
			}
    		if (isset($criteria['owner']) && !empty($criteria['owner'])) {
                /*$select->join(array('uf' => 'user_feed'), 
                              'uf.feed_oid = feed.oid', null)
                       ->where('uf.user_oid = ?', $criteria['owner']);*/
    			$select->where('feed.user_oid = ?', $criteria['owner']);
            }
			if (isset($criteria['contains']) && !empty($criteria['contains'])) {
				$contains = '%'.$criteria['contains'].'%';
				
                $select->where("UPPER(feed.name) like UPPER('$contains') or ".
                               "UPPER(feed.url) like UPPER('$contains') or ".
                               "UPPER(feed.description) like UPPER('$contains')"); 
            }
		}
		
		//die($select->__toString());
		return new Model_ZendDb_Paginator_SearchFeed($select);
	}
	
    /**
     * Update last sync timestamp on a given feed
     * 
     * @param int $feedID
     * @return void
     * @see application/models/Model_FeedService#updateLastSync($feedID)
	 */
	public function updateLastSync($feedID) {
	   $timestamp = date('Y-m-d H:i:s', time());
	   $this->_feed->update(array('lastSync' => $timestamp), 
	                        array('oid' => $feedID));	
	}
	
	/**
	 * Add a new Feed
     * 
     * @param Model_Entity_Feed $feed
     * @return void
	 * @see application/models/Model_FeedService#addFeed($feed)
	 */
	public function addFeed(Model_Entity_Feed $feed)
	{
	    $feedID = 0;
        $feedData = $feed->getOptions();
        
        $feedData['user_oid'] = $feed->getCreatedBy()->getOid();
        
        unset($feedData['oid']);
        unset($feedData['news']);
        unset($feedData['owners']);
        unset($feedData['createdBy']);
        unset($feedData['category']);
        $feedData['lastSync'] = date('Y-m-d H:i:s', time());
        
        try {
            $this->_db->beginTransaction();
            
            $feedSearch = $this->isfeedPresent($feed->url);
            // Check if feed is already present 
            if (null == $feedSearch) {
                $feedID = $this->_feed->insert($feedData);
            } else {
                $feedID = $feedSearch->oid; // If so reuse it!
            }

            $feed->setOid($feedID);
            
            // Check if the feed already is in the choosen category
            if (!$this->isFeedInCategory($feed)) {
            	$this->_feedCategory->insert(array(
                    'category_oid' => $feed->category->oid,
                    'feed_oid' => $feedID
                ));
            }
            
            $this->_db->commit();
        } catch (Exception $e) {
            $this->_db->rollBack();
            
            throw $e;
        }
	}
	
   /**
     * Add a new User Feed
     * 
     * @param Model_Entity_Feed $feed
     * @return void
     * @see application/models/Model_FeedService#addFeed($feed)
     */
    public function addUserFeed(Model_Entity_Feed $feed)
    {
        $feedID = 0;
        $feedData = $feed->getOptions();
        
        $feedData['user_oid'] = $feed->getCreatedBy()->getOid();
        
        unset($feedData['oid']);
        unset($feedData['news']);
        unset($feedData['owners']);
        unset($feedData['createdBy']);
        unset($feedData['category']);
        $feedData['lastSync'] = date('Y-m-d H:i:s', time());
        
        try {
            $this->_db->beginTransaction();
            
            $feedSearch = $this->isfeedPresent($feed->url);
            // Check if feed is already present 
            if (null == $feedSearch) {
                $feedID = $this->_feed->insert($feedData);
            } else {
                $feedID = $feedSearch->oid; // If so reuse it!
            }

            $feed->setOid($feedID);
            
            // Check if the user already has the feed being added
            if (!$this->isFeedAlreadyOwned($feed)) {
                $this->_feedUser->insert(array(
                    'feed_oid'     => $feedID,
                    'user_oid'     => $feedData['user_oid'],
                    'category_oid' => $feed->category->oid
                ));
                
                // Check if the feed already is in the choosen category
                if (!$this->isFeedInCategory($feed)) {
	                $this->_feedCategory->insert(array(
	                    'category_oid' => $feed->category->oid,
	                    'feed_oid' => $feedID
	                ));
                }
            }
            
            $this->_db->commit();
        } catch (Exception $e) {
            $this->_db->rollBack();
            
            throw $e;
        }
    }

    /**
     * Delete a feed and it's collection of news
     * 
     * @param int  $feedID
     * @return void
     * @see application/models/Model_FeedService#deleteFeed($feedID)
     */
	public function deleteFeed($feedID) {
		if ($this->hasFeedOwners($feedID)) {
			throw new Model_ZendDb_CantDeleteException();
		}
		
		try {
			$this->_db->beginTransaction();
			
			$this->_news->delete(array('feed_oid = ?' => $feedID));
			
			$this->_feedCategory->delete(array('feed_oid = ?' => $feedID));
			
			$this->_homePageBox->delete(array('feed_oid = ?' => $feedID));
			
			$this->_feed->delete(array('oid = ?' => $feedID));
			
			$this->_db->commit();
		} catch (Exception $e) {
            $this->_db->rollBack();
            
            throw $e;
        }
	}

    /**
     * Delete a user feed and it's collection of news
     * 
     * @param Model_Entity_Feed $feed
     * @return void
     * @see application/models/Model_FeedService#delteUserFeed($feed)
     */
	public function deleteUserFeed(Model_Entity_Feed $feed) {
	   try {
            $this->_db->beginTransaction();
            
            $select = $this->_news->select();
            $select->where('feed_oid = ?', $feed->oid);
            
            $newsRowSet = $this->_news->fetchAll($select);
            
            foreach($newsRowSet as $newsRow) {
                $this->_userNews->delete(array(
                    'news_oid = ?' => $newsRow->oid,
                    'user_oid = ?' => $feed->owners->oid
                ));
            }
            
            $this->_feedUser->delete(array(
                'feed_oid = ?' => $feed->oid,
                'user_oid = ?' => $feed->owners->oid
            ));
            
            //Delete also from news and feed tables if it can
            if (!$this->hasFeedOwners($feed->oid) && 
                !$this->isFeedInHomepageBox($feed->oid)) {
                	
                $this->_news->delete(array('feed_oid = ?' => $feed->oid));
                
                $this->_feedCategory->delete(array('feed_oid = ?' => $feed->oid));
                
                $this->_feed->delete(array('oid = ?' => $feed->oid));
            }
            
            $this->_db->commit();
        } catch (Exception $e) {
            $this->_db->rollBack();
            
            throw $e;
        }
	}
}
